import pandas as pandas
#import xlrd
#xls = xlrd.open_workbook(r'Stock_Data.xlsx', on_demand=True)
#print xls.sheet_names()
The sheet names are [u'Original', u'deduped', u'Sheet7', u'AmericanExchanges', u'ForeignAndPink', u'Sheet2']
data = pandas.read_excel('Stock_Data.xlsx', 'AmericanExchanges')
import copy
data_backup=copy.deepcopy(data)
First we create a date column instead of the integer formatting
data['Dates']=pandas.to_datetime(data['Names Date'].values, format = '%Y%m%d')
data = data[pandas.notnull(data['Company Name'])]
pcosset = set(data['PERMCO'].values)
pcoslist = list(set(data['PERMCO'].values))
# Make sure no Permco's are repeated
assert len(pcosset) ==len(pcoslist)
import csv
with open('w_pco.csv', 'wb') as csvfile:
w = csv.writer(csvfile, delimiter=' ')
for p in pcosset:
cpnies = list(set(data['Company Name'][data['PERMCO']==p]))
row = str(p) + ', '+ ', '.join(cpnies)
w.writerow(row)
for p in pcosset:
cpnies = list(set(data['Company Name'][data['PERMCO']==p]))
row = str(p) + ', '+ ', '.join(cpnies)
fig = plt.figure()
fig.set_size_inches(16,4)
to_plot =data[data['PERMCO'] == p]
to_plot.plot('Dates', 'Price or Bid/Ask Average', subplots=True, title =row)
plt.show()
plt.close()